// license-header java merge-point
/**
 * This is only generated once! It will never be overwritten. You can (and have to!) safely modify it by hand.
 */
package org.tinaelle.server.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.PersistentClass;
import org.hibernate.mapping.Property;
import org.hibernate.mapping.RootClass;
import org.hibernate.mapping.Table;
import org.hibernate.persister.entity.SingleTableEntityPersister;
import org.springframework.orm.hibernate3.LocalSessionFactoryBean;
import org.tinaelle.BeanLocator;
import org.tinaelle.Search;
import org.tinaelle.SearchParameter;
import org.tinaelle.ServiceLocator;
import org.tinaelle.common.criteria.AdColumnSearchCriteria;
import org.tinaelle.common.dto.AdColumnDTO;
import org.tinaelle.common.dto.AdElementDTO;
import org.tinaelle.common.dto.AdEntityTypeDTO;
import org.tinaelle.common.dto.AdLanguageDTO;
import org.tinaelle.common.dto.AdParameterValueDTO;
import org.tinaelle.common.dto.AdReferenceDTO;
import org.tinaelle.common.dto.AdReferenceListDTO;
import org.tinaelle.common.dto.AdReferenceListTrlDTO;
import org.tinaelle.common.dto.AdReferenceTableDTO;
import org.tinaelle.common.dto.AdReferenceTrlDTO;
import org.tinaelle.common.dto.AdTableDTO;
import org.tinaelle.common.dto.AdValRuleDTO;
import org.tinaelle.server.domain.AdColumn;
import org.tinaelle.server.domain.AdColumnDao;
import org.tinaelle.server.domain.AdElementDao;
import org.tinaelle.server.domain.AdEntityTypeDao;
import org.tinaelle.server.domain.AdReference;
import org.tinaelle.server.domain.AdReferenceDao;
import org.tinaelle.server.domain.AdReferenceListDao;
import org.tinaelle.server.domain.AdReferenceTableDao;
import org.tinaelle.server.domain.AdTable;
import org.tinaelle.server.domain.AdTableDao;
import org.tinaelle.server.domain.AdValRuleDao;
import org.tinaelle.utils.BootstrapUtils;

import com.atomikos.jdbc.AtomikosDataSourceBean;

/**
 * @see org.tinaelle.service.AdSystemService
 */
public class AdSystemServiceImpl extends org.tinaelle.server.service.AdSystemServiceBase {
	/**
     * 
     */
	private static final long	serialVersionUID	= 1L;
	private final Logger	  logger	         = Logger.getLogger( this.getClass() );

	/**
	 * @see org.tinaelle.service.AdSystemService#getTableByName(java.lang.String)
	 */
	@Override
	protected org.tinaelle.common.dto.AdTableDTO handleGetTableByName(final java.lang.String name)
	        throws java.lang.Exception {
		return ( AdTableDTO ) getAdTableDao().findByName( AdTableDao.TRANSFORM_ADTABLEDTO, name );
	}

	/**
	 * @see org.tinaelle.service.AdSystemService#getTableByTableName(java.lang.String)
	 */
	@Override
	protected org.tinaelle.common.dto.AdTableDTO handleGetTableByTableName(final java.lang.String tableName)
	        throws java.lang.Exception {
		return ( AdTableDTO ) getAdTableDao().findByTableName( AdTableDao.TRANSFORM_ADTABLEDTO, tableName );
	}

	@Override
	protected AdColumnDTO handleGetColumnByColumnName(final AdTableDTO adTableDTO, final String columnName)
	        throws Exception {
		final AdColumnSearchCriteria searchCriteria = new AdColumnSearchCriteria();
		searchCriteria.setAdTableId( adTableDTO.getAdTableId() );
		searchCriteria.setColumnName( columnName );
		final Collection<?> result = getAdColumnDao()
		        .findByCriteria( AdColumnDao.TRANSFORM_ADCOLUMNDTO, searchCriteria );
		if ( result.size() > 0 ) {
			return result.toArray( new AdColumnDTO[ 0 ] )[ 0 ];
		} else {
			return null;
		}
	}

	@Override
	protected AdColumnDTO[] handleGetColumns(final AdTableDTO adTableDTO) throws Exception {
		final AdTable adTable = getAdTableDao().load( adTableDTO.getAdTableId() );
		final Collection<AdColumn> adColumns = adTable.getAdColumns();

		getAdColumnDao().toAdColumnDTOCollection( adColumns );

		return adColumns.toArray( new AdColumnDTO[ 0 ] );
	}

	@Override
	protected AdReferenceDTO handleGetReferenceByInternalName(final String internalName) throws Exception {
		return ( AdReferenceDTO ) getAdReferenceDao().findByInternalName( AdReferenceDao.TRANSFORM_ADREFERENCEDTO,
		        internalName );
	}

	@Override
	protected AdReferenceTrlDTO handleGetReferenceByInternalName(final String internalName, final AdLanguageDTO language)
	        throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected AdReferenceListDTO handleGetReferenceListByName(final String adReferenceInternalName, final String name)
	        throws Exception {
		final AdReference adReference = getAdReferenceDao().findByInternalName( adReferenceInternalName );
		return ( AdReferenceListDTO ) getAdReferenceListDao().findByName(
		        AdReferenceListDao.TRANSFORM_ADREFERENCELISTDTO, adReference, name );
	}

	@Override
	protected AdReferenceListTrlDTO handleGetReferenceListByName(final String adReferenceInternalName,
	        final String name, final AdLanguageDTO language) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected AdReferenceListDTO handleGetReferenceListByValue(final String adReferenceInternalName, final String value)
	        throws Exception {
		final AdReference adReference = getAdReferenceDao().findByInternalName( adReferenceInternalName );
		return ( AdReferenceListDTO ) getAdReferenceListDao().findByValue(
		        AdReferenceListDao.TRANSFORM_ADREFERENCELISTDTO, adReference, value );
	}

	@Override
	protected AdReferenceListTrlDTO handleGetReferenceListByValue(final String adRererenceInternalName,
	        final String value, final AdLanguageDTO language) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected AdReferenceTableDTO handleGetReferenceTableByInternalName(String adReferenceInternalName)
	        throws Exception {
		AdReference adReference = getAdReferenceDao().findByInternalName( adReferenceInternalName );
		Search searchObject = new Search();
		searchObject.addSearchParameter( new SearchParameter( "adReference", adReference ) );
		Set<AdReferenceTableDTO> result = ( Set<AdReferenceTableDTO> ) getAdReferenceTableDao().search(
		        AdReferenceTableDao.TRANSFORM_ADREFERENCETABLEDTO, searchObject );
		if ( result.size() > 0 ) {
			return result.toArray( new AdReferenceTableDTO[ 0 ] )[ 0 ];
		} else {
			return null;
		}
	}

	@Override
	protected void handleFillInitialValues() throws Exception {
		/**
		 * Load Entity Types
		 */
		fillInitialValuesForEntityType();

		/**
		 * Load Tables
		 */
		fillInitialValuesForTable();

		/**
		 * Load References
		 */
		fillInitialValuesForReference();

		/**
		 * Load Reference Lists
		 */
		fillInitialValuesForReferenceList();

		/**
		 * Load Reference Tables
		 */
		fillInitialValuesForReferenceTable();

		/**
		 * Load colums
		 */
		fillInitialValuesForColumn();
	}

	@Override
	protected AdEntityTypeDTO handleGetEntityTypeByName(final String name) throws Exception {
		return ( AdEntityTypeDTO ) getAdEntityTypeDao().findByName( AdEntityTypeDao.TRANSFORM_ADENTITYTYPEDTO, name );
	}

	@Override
	protected void handleCreateOrUpdateTableDataFromDB(final AdTableDTO adTableDTO) throws Exception {
		// BeanLocator.instance().getContext().getBeanDefinitionNames();

		logger.info( "handleCreateOrUpdateTableDataFromDB() :: START :: TableName = " + adTableDTO.getName() );
		com.atomikos.jdbc.AtomikosDataSourceBean ds = ( AtomikosDataSourceBean ) BeanLocator.instance().getBean(
		        "dataSource" );

		// Session s = SessionFactoryUtils.getSession( ( SessionFactory ) BeanLocator.instance().getBean(
		// "sessionFactory" ), false );
		DatabaseMetaData dbmd = null;
		Connection con = null;
		try {
			con = ds.getConnection();
			// con.setTransactionIsolation( Connection.TRANSACTION_NONE );
			con.setReadOnly( true );
			dbmd = con.getMetaData();
		} catch ( final Exception e ) {
			e.printStackTrace();
			return;
		}
		ResultSet rs = dbmd.getColumns( con.getCatalog(), con.getMetaData().getUserName(), adTableDTO.getTableName(),
		        null );

		addTableColumn( rs, adTableDTO );

		rs.close();

		rs = dbmd.getImportedKeys( con.getCatalog(), con.getMetaData().getUserName(), adTableDTO.getTableName() );

		addTableForeignColumns( rs, adTableDTO );

		rs.close();

		if ( con != null ) {
			con.close();
		}
		logger.info( "handleCreateOrUpdateTableDataFromDB() :: END :: TableName = " + adTableDTO.getName() );
	}

	private void addTableForeignColumns(final ResultSet rs, final AdTableDTO adTableDTO) throws Exception {
		logger.info( "addTableForeignColumns() :: START :: TableName = " + adTableDTO.getName() );
		int i = -1;
		while ( rs.next() ) {
			i++;
			final ServiceLocator serviceLocator = ServiceLocator.instance();
			serviceLocator.getAdI18NService();
			final AdParameterService adParamDetailSrv = serviceLocator.getAdParameterService();
			final AdSystemService adSystemSrv = serviceLocator.getAdSystemService();

			// primary key table catalog being imported (may be null)
			final String pkTableCat = rs.getString( "PKTABLE_CAT" );
			// primary key table schema being imported (may be null)
			final String pkTableSchema = rs.getString( "PKTABLE_SCHEM" );
			// primary key table name being imported
			final String pkTableName = rs.getString( "PKTABLE_NAME" );
			// primary key column name being imported
			final String pkColumnName = rs.getString( "PKCOLUMN_NAME" );
			// foreign key table catalog (may be null)
			final String fkTableCat = rs.getString( "FKTABLE_CAT" );
			// foreign key table schema (may be null)
			final String fkTableSchema = rs.getString( "FKTABLE_SCHEM" );
			// foreign key table name
			final String fkTableName = rs.getString( "FKTABLE_NAME" );
			// foreign key column name
			final String fkColumnName = rs.getString( "FKCOLUMN_NAME" );
			// sequence number within a foreign key( a value of 1 represents the first column of the foreign key, a
			// value of 2 would represent the second column within the foreign key).
			final short keySeq = rs.getShort( "KEY_SEQ" );
			/*
			 What happens to a foreign key when the primary key is updated:
					importedNoAction - do not allow update of primary key if it has been imported
					importedKeyCascade - change imported key to agree with primary key update
					importedKeySetNull - change imported key to NULL if its primary key has been updated
					importedKeySetDefault - change imported key to default values if its primary key has been updated
			 */
			final short updateRule = rs.getShort( "UPDATE_RULE" );
			/*
			 What happens to the foreign key when primary is deleted.
					importedKeyNoAction - do not allow delete of primary key if it has been imported
					importedKeyCascade - delete rows that import a deleted key
					importedKeySetNull - change imported key to NULL if its primary key has been deleted
					importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
					importedKeySetDefault - change imported key to default if its primary key has been deleted
			 */
			final short deleteRule = rs.getShort( "DELETE_RULE" );
			// foreign key table name
			final String fkName = rs.getString( "FK_NAME" );
			// primary key name (may be null)
			final String pkName = rs.getString( "PK_NAME" );
			/*
			 can the evaluation of foreign key constraints be deferred until commit
					importedKeyInitiallyDeferred - see SQL92 for definition
					importedKeyInitiallyImmediate - see SQL92 for definition
					importedKeyNotDeferrable - see SQL92 for definition
			 */
			final short deferrability = rs.getShort( "DEFERRABILITY" );

			logger.info( "addTableForeignColumns() :: " + i + ". pkTableCat = " + pkTableCat + ", pkTableSchema = "
			        + pkTableSchema + ", pkTableName = " + pkTableName + ", pkColumnName = " + pkColumnName );
			logger.info( "addTableForeignColumns() :: " + i + ". fkTableCat = " + fkTableCat + ", fkTableSchema = "
			        + fkTableSchema + ", fkTableName = " + fkTableName + ", fkColumnName = " + fkColumnName );
			logger.info( "addTableForeignColumns() :: " + i + ". keySeq = " + keySeq + ", updateRule = " + updateRule
			        + ", deleteRule = " + deleteRule + ", fkName = " + fkName + ", pkName = " + pkName
			        + ", deferrability = " + deferrability );
			if ( fkColumnName.endsWith( "_ID" ) ) {
				continue;
			}
			if ( pkTableName.equalsIgnoreCase( "AD_PARAMETER_VALUE" ) ) {
				final AdParameterValueDTO fkParam = adParamDetailSrv.getParameterValueByName( "LIST", fkColumnName );
				final AdColumnDTO pkAdColumn = adSystemSrv.getColumnByColumnName( adTableDTO, fkColumnName );

				if ( pkAdColumn == null ) {
					continue;
				}

				logger.info( "addTableForeignColumns() :: Set " + pkColumnName + " to " + fkColumnName );
				pkAdColumn.setAdReferenceId( adParamDetailSrv.getParameterValueByName( "REFERENCE", "LIST" )
				        .getAdParameterValueId() );
				pkAdColumn.setAdReferenceValueId( fkParam.getAdParameterValueId() );

				adSystemSrv.save( pkAdColumn );
			} else {
				// logger.info( "addTableForeignColumns() :: Table : " + adTableDTO.getName() + ", Ignore PkColumn : " +
				// pkColumnName + ", FkColumn : " + fkColumnName );
			}
		}
		rs.close();
		logger.info( "addTableForeignColumns() :: END :: TableName = " + adTableDTO.getName() );
	}

	private void addTableColumn(final ResultSet rs, final AdTableDTO adTableDTO) throws Exception {
		final String tableName = adTableDTO.getTableName();
		logger.info( "addTableColumn() :: START :: TableName = " + adTableDTO.getName() );
		while ( rs.next() ) {
			final ServiceLocator serviceLocator = ServiceLocator.instance();
			final AdTextService adTexteSrv = serviceLocator.getAdTextService();

			final String tn = rs.getString( "TABLE_NAME" ).toUpperCase();
			final String columnName = rs.getString( "COLUMN_NAME" );
			AdTableDTO tnDTO = getTableByTableName( tn );
			logger.info( "Current Table/Column :" + tn + " / " + columnName );
			lookupTable( tableName );
			final RootClass rootClass = lookupClass( tableName );
			if ( tnDTO.getName().equalsIgnoreCase( rootClass.getEntityName() ) ) {
				tnDTO.setName( rootClass.getEntityName() );
				tnDTO = save( tnDTO );
			}
			if ( !tableName.equalsIgnoreCase( tn ) ) {
				continue;
			}

			AdColumnDTO adColumn = getColumnByColumnName( adTableDTO, columnName );
			if ( adColumn != null ) {
				continue;
			}
			final int dataType = rs.getInt( "DATA_TYPE" );
			final String typeName = rs.getString( "TYPE_NAME" );
			final String nullable = rs.getString( "IS_NULLABLE" );
			int size = rs.getInt( "COLUMN_SIZE" );
			rs.getInt( "DECIMAL_DIGITS" );

			//
			adColumn = new AdColumnDTO();
			adColumn.setAdTableId( adTableDTO.getAdTableId() );

			adColumn.setAdEntityTypeId( getEntityTypeByName( "Tinaelle" ).getAdEntityTypeId() );
			// getAdElementDao().loadAll( AdElementDao.TRANSFORM_ADELEMENTDTO );

			ArrayList<AdElementDTO> adElements = ( ArrayList ) getAdElementDao().findByColumnName(
			        AdElementDao.TRANSFORM_ADELEMENTDTO, columnName );
			AdElementDTO adElementDTO;

			if ( adElements.size() == 0 ) {
				adElementDTO = new AdElementDTO();

				adElementDTO.setColumnName( columnName );
				adElementDTO.setTableName( tableName );

				adElementDTO.setValue( lookupColumn( rootClass, columnName ) );
				adElementDTO.setName( adTableDTO.getName() + "." + adElementDTO.getValue() );
				adElementDTO.setAdEntityTypeId( getEntityTypeByName( "Tinaelle" ).getAdEntityTypeId() );

				if ( columnName.equalsIgnoreCase( tableName + "_ID" ) ) {
					adElementDTO.setColumnName( tableName + "_ID" );
					// adElement.setName(adTableDTO.getName());
				}

				adElementDTO = adTexteSrv.save( adElementDTO );
			} else {
				adElementDTO = adElements.get( 0 );
			}
			adColumn.setColumnName( adElementDTO.getColumnName() );
			adColumn.setName( adElementDTO.getName() );
			adColumn.setDescription( adElementDTO.getDescription() );
			adColumn.setHelp( adElementDTO.getHelp() );
			adColumn.setAdElementId( adElementDTO.getAdElementId() );
			adColumn.setIsIdentifier( Boolean.FALSE );
			adColumn.setIsTranslated( Boolean.FALSE );
			adColumn.setIsUpdateable( Boolean.TRUE );
			//
			adColumn.setIsMandatory( "NO".equals( nullable ) );
			// Key
			if ( columnName.equalsIgnoreCase( tableName + "_ID" ) ) {
				adColumn.setIsKey( true );
				adColumn.setAdReferenceId( getReferenceByInternalName( "ID" ).getAdReferenceId() );
				adColumn.setIsUpdateable( false );
			}
			// ID
			else if ( columnName.toUpperCase().endsWith( "_ID" ) ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "TABLE_DIR" ).getAdReferenceId() );
			} else if ( ( dataType == Types.DATE ) || ( dataType == Types.TIME ) || ( dataType == Types.TIMESTAMP )
			// || columnName.toUpperCase().indexOf("DATE") != -1
			        || columnName.equalsIgnoreCase( "Created" ) || columnName.equalsIgnoreCase( "Updated" ) ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "DATE_TIME" ).getAdReferenceId() );
			} else if ( columnName.equalsIgnoreCase( "Created_By" ) || columnName.equalsIgnoreCase( "Updated_By" ) ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "TABLE" ).getAdReferenceId() );
				adColumn.setAdReferenceValueId( getReferenceTableByInternalName( "AD_USER" ).getAdReferenceId() );
				adColumn.setIsUpdateable( false );
			}
			// Entity Type
			else if ( columnName.equalsIgnoreCase( "EntityType" ) ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "TABLE" ).getAdReferenceId() );
				adColumn.setAdReferenceValueId( getReferenceTableByInternalName( "ENTITY_TYPE" ).getAdReferenceId() );
			}
			// CLOB
			else if ( dataType == Types.CLOB ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "TEXT_LONG" ).getAdReferenceId() );
			} else if ( dataType == Types.BLOB ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "BINARY" ).getAdReferenceId() );
			} else if ( columnName.toUpperCase().indexOf( "AMT" ) != -1 ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "AMOUNT" ).getAdReferenceId() );
			} else if ( columnName.toUpperCase().indexOf( "QTY" ) != -1 ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "QUANTITY" ).getAdReferenceId() );
			} else if ( ( size == 1 )
			        && ( columnName.toUpperCase().startsWith( "IS" ) || columnName.toUpperCase().startsWith( "HAS" ) || ( dataType == Types.CHAR ) ) ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "YES_NO" ).getAdReferenceId() );
			} else if ( columnName.equalsIgnoreCase( "Name" ) ) {
				adColumn.setAdReferenceId( getReferenceByInternalName( "STRING" ).getAdReferenceId() );
				adColumn.setIsIdentifier( true );
				adColumn.setSeqNo( 1 );
			}
			// String, Text
			else if ( ( dataType == Types.CHAR ) || ( dataType == Types.VARCHAR ) || typeName.startsWith( "NVAR" )
			        || typeName.startsWith( "NCHAR" ) ) {
				if ( typeName.startsWith( "N" ) ) {
					size /= 2;
				}
				if ( size > 255 ) {
					adColumn.setAdReferenceId( getReferenceByInternalName( "TEXT" ).getAdReferenceId() );
				} else {
					adColumn.setAdReferenceId( getReferenceByInternalName( "STRING" ).getAdReferenceId() );
				}
			}
			// Number
			else if ( ( dataType == Types.INTEGER ) || ( dataType == Types.SMALLINT ) || ( dataType == Types.DECIMAL )
			        || ( dataType == Types.NUMERIC ) ) {
				if ( size == 10 ) {
					adColumn.setAdReferenceId( getReferenceByInternalName( "INTEGER" ).getAdReferenceId() );
				} else {
					adColumn.setAdReferenceId( getReferenceByInternalName( "NUMBER" ).getAdReferenceId() );
				}
			}
			// ??
			else {
				adColumn.setAdReferenceId( getReferenceByInternalName( "STRING" ).getAdReferenceId() );
			}

			adColumn.setFieldLength( new Integer( size ) );
			if ( adColumn.getIsUpdateable().booleanValue()
			        && ( columnName.equalsIgnoreCase( "AD_Client_ID" ) || columnName.equalsIgnoreCase( "AD_Org_ID" )
			                || columnName.toUpperCase().startsWith( "CREATED" )
			                || columnName.toUpperCase().equals( "UPDATED" ) || columnName.equalsIgnoreCase( "VERSION" ) ) ) {
				adColumn.setIsUpdateable( false );
			}

			// Done
			save( adColumn );
		} // while columns
		logger.info( "addTableColumn() :: END :: TableName = " + adTableDTO.getName() );
	} // addTableColumn

	protected PersistentClass extractPersistentClass(final String entityName) {
		PersistentClass pc = null;
		LocalSessionFactoryBean sessionFactory = null;

		try {
			sessionFactory = ( LocalSessionFactoryBean ) BeanLocator.instance().getBean( "&sessionFactory" );
			pc = ( sessionFactory ).getConfiguration().getClassMapping( entityName );
		} // end try
		catch ( final NullPointerException npe ) {
			logger
			        .error( "NullPointerException - make sure you get the right session factory " + npe.getMessage(),
			                npe );
		} // end catch

		return pc;
	} // end extractPersistentClass()

	protected String extractPrimaryKeyColumn(final PersistentClass localPC) {
		String pkColumn = null;

		try {
			pkColumn = ( ( Column ) localPC.getIdentifierProperty().getColumnIterator().next() ).getName();
		} // end try
		catch ( final NullPointerException npe ) {
			logger.info( "NullPointerException " + npe.getMessage() );
		} // end catch

		return pkColumn;
	} // end extractPrimaryKeyColumn()

	protected Table lookupTable(final String tableName) {
		final LocalSessionFactoryBean sf = ( LocalSessionFactoryBean ) BeanLocator.instance().getBean(
		        "&sessionFactory" );
		for ( Iterator<Table> i = sf.getConfiguration().getTableMappings(); i.hasNext(); ) {
			Table tmp = i.next();
			if ( tmp.getName().equalsIgnoreCase( tableName ) ) {
				return tmp;
			}
		}

		return null;
	}

	protected RootClass lookupClass(final String tableName) {
		final LocalSessionFactoryBean sf = ( LocalSessionFactoryBean ) BeanLocator.instance().getBean(
		        "&sessionFactory" );
		for ( Iterator<RootClass> i = sf.getConfiguration().getClassMappings(); i.hasNext(); ) {
			RootClass tmp = i.next();
			if ( tmp.getTable().getName().equalsIgnoreCase( tableName ) ) {
				return tmp;
			}
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	protected SingleTableEntityPersister lookupEntityByTableName(final String tableName) {
		final LocalSessionFactoryBean sf = ( LocalSessionFactoryBean ) BeanLocator.instance().getBean(
		        "&sessionFactory" );
		sf.getConfiguration().getTableMappings().next();
		RootClass entity = null;
		for ( Iterator<RootClass> i = sf.getConfiguration().getClassMappings(); i.hasNext(); ) {
			RootClass tmp = i.next();
			if ( tmp.getTable().getName().equalsIgnoreCase( tableName ) ) {
				entity = tmp;
				break;
			}
		}
		if ( entity != null ) {
			entity.getEntityPersisterClass();
			final Map allClassMetaData = entity.getMetaAttributes();
			for ( final Iterator<SingleTableEntityPersister> i = allClassMetaData.values().iterator(); i.hasNext(); ) {
				final SingleTableEntityPersister cm = i.next();

				if ( cm.getTableName().equalsIgnoreCase( tableName ) ) {
					return cm;
				}
			}
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	protected String lookupColumn(final RootClass rootClass, final String columnName) {
		for ( Iterator<Property> i = rootClass.getPropertyIterator(); i.hasNext(); ) {
			Property p = i.next();
			for ( Iterator<Column> ii = p.getColumnIterator(); ii.hasNext(); ) {
				Column c = ii.next();
				if ( c.getName().equalsIgnoreCase( columnName ) ) {
					return p.getName();
				}
			}
		}

		for ( Iterator<Column> ii = rootClass.getIdentifierProperty().getColumnIterator(); ii.hasNext(); ) {
			Column c = ii.next();
			if ( c.getName().equalsIgnoreCase( columnName ) ) {
				return rootClass.getIdentifierProperty().getName();
			}
		}

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	protected void handleFillInitialValuesForColumn() throws Exception {
		logger.info( "handleFillInitialValuesForColumn() :: START" );
		Collection<AdTableDTO> allTables = ( Collection<AdTableDTO> ) getAdTableDao().loadAll(
		        AdTableDao.TRANSFORM_ADTABLEDTO );
		logger.info( "handleFillInitialValuesForColumn() :: Nb tables = " + allTables.size() );
		for ( Iterator<AdTableDTO> i = allTables.iterator(); i.hasNext(); ) {
			handleCreateOrUpdateTableDataFromDB( i.next() );
		}
		logger.info( "handleFillInitialValuesForColumn() :: END" );
	}

	@Override
	protected void handleFillInitialValuesForEntityType() throws Exception {
		// File Format :
		// Name, Description, Help, : 0, 1, 2
		// Entity Version : 3
		String[][] entityTypeContent = BootstrapUtils.instance().getBootstrapSheetContent( "AdEntityType" );
		for ( int i = 1; i < entityTypeContent.length; i++ ) {
			final String[] entityTypeLine = entityTypeContent[ i ];

			if ( entityTypeLine.length == 4 ) {
				AdEntityTypeDTO adEntityTypeDTO = getEntityTypeByName( entityTypeLine[ 0 ].trim() );
				if ( adEntityTypeDTO == null ) {
					adEntityTypeDTO = new AdEntityTypeDTO();

					adEntityTypeDTO.setName( entityTypeLine[ 0 ].trim() );
					adEntityTypeDTO.setDescription( entityTypeLine[ 1 ].trim() );
					adEntityTypeDTO.setHelp( entityTypeLine[ 2 ].trim() );
					adEntityTypeDTO.setEntityVersion( entityTypeLine[ 3 ].trim() );

					adEntityTypeDTO = save( adEntityTypeDTO );
				}
			}
		}
	}

	@Override
	protected void handleFillInitialValuesForReference() throws Exception {
		// File Format :
		// Name, Description, Internal Name : 0, 1, 2
		// Validation Type, AD_Entity_Type : 3, 4
		String[][] referenceContent = BootstrapUtils.instance().getBootstrapSheetContent( "AdReference" );
		for ( int i = 1; i < referenceContent.length; i++ ) {
			final String[] referenceLine = referenceContent[ i ];

			if ( referenceLine.length == 5 ) {
				AdReferenceDTO adReferenceDTO = getReferenceByInternalName( referenceLine[ 2 ].trim() );
				if ( adReferenceDTO == null ) {
					adReferenceDTO = new AdReferenceDTO();
				}
				adReferenceDTO.setName( referenceLine[ 0 ].trim() );
				adReferenceDTO.setDescription( referenceLine[ 1 ].trim() );
				adReferenceDTO.setInternalName( referenceLine[ 2 ].trim() );
				logger.info( "handleFillInitialValuesForReference() :: Lookup " + referenceLine[ 3 ].trim() );
				AdReferenceListDTO validationType = getReferenceListByValue( "VALIDATION_TYPE", referenceLine[ 3 ]
				        .trim() );
				adReferenceDTO.setValidationType( validationType.getAdReferenceListId() );
				adReferenceDTO.setAdEntityTypeId( getEntityTypeByName( referenceLine[ 4 ].trim() ).getAdEntityTypeId() );

				adReferenceDTO = save( adReferenceDTO );
			}
		}
	}

	@Override
	protected void handleFillInitialValuesForReferenceList() throws Exception {
		// File Format :
		// Name, Value, Entity Type, : 0, 1, 2
		// Ad Reference - Internal Name : 3
		String[][] referenceListContent = BootstrapUtils.instance().getBootstrapSheetContent( "AdReferenceList" );
		for ( int i = 1; i < referenceListContent.length; i++ ) {
			final String[] referenceListLine = referenceListContent[ i ];

			if ( referenceListLine.length == 4 ) {
				AdReferenceListDTO adReferenceListDTO = getReferenceListByValue( referenceListLine[ 3 ].trim(),
				        referenceListLine[ 1 ].trim() );
				if ( adReferenceListDTO == null ) {
					adReferenceListDTO = new AdReferenceListDTO();
				}

				final AdReferenceDTO adReferenceDTO = getReferenceByInternalName( referenceListLine[ 3 ].trim() );

				adReferenceListDTO.setName( referenceListLine[ 0 ].trim() );
				adReferenceListDTO.setValue( referenceListLine[ 1 ].trim() );
				adReferenceListDTO.setAdEntityTypeId( getEntityTypeByName( referenceListLine[ 2 ].trim() )
				        .getAdEntityTypeId() );
				adReferenceListDTO.setAdReferenceId( adReferenceDTO.getAdReferenceId() );

				adReferenceListDTO = save( adReferenceListDTO );
			}
		}
	}

	@Override
	protected void handleFillInitialValuesForReferenceTable() throws Exception {
		// File Format :
		// AdTable, whereClause, orderClause, : 0, 1, 2
		// IsValueDisplayed, Display, Key, : 3, 4, 5
		// Ad Reference - Internal Name, Entity Type : 6, 7
		String[][] referenceTableContent = BootstrapUtils.instance().getBootstrapSheetContent( "AdReferenceTable" );
		for ( int i = 1; i < referenceTableContent.length; i++ ) {
			final String[] referenceTableLine = referenceTableContent[ i ];

			if ( referenceTableLine.length == 8 ) {
				AdReferenceTableDTO adReferenceTableDTO = getReferenceTableByInternalName( referenceTableLine[ 6 ]
				        .trim() );
				if ( adReferenceTableDTO == null ) {
					adReferenceTableDTO = new AdReferenceTableDTO();
				}

				final AdReferenceDTO adReferenceDTO = getReferenceByInternalName( referenceTableLine[ 6 ].trim() );
				final AdTableDTO adTableDTO = getTableByTableName( referenceTableLine[ 0 ] );
				adReferenceTableDTO.setAdTableId( adTableDTO.getAdTableId() );
				adReferenceTableDTO.setWhereClause( referenceTableLine[ 1 ].trim().length() == 0 ? null
				        : referenceTableLine[ 1 ].trim() );
				adReferenceTableDTO.setOrderClause( referenceTableLine[ 2 ].trim().length() == 0 ? null
				        : referenceTableLine[ 2 ].trim() );
				adReferenceTableDTO.setIsValueDisplayed( referenceTableLine[ 3 ].trim().equalsIgnoreCase( "TRUE" )
				        || referenceTableLine[ 3 ].trim().equalsIgnoreCase( "Y" )
				        || referenceTableLine[ 3 ].trim().equalsIgnoreCase( "1" ) );
				if ( referenceTableLine[ 4 ].length() > 0 ) {
					adReferenceTableDTO.setAdDisplayId( getColumnByColumnName( adTableDTO, referenceTableLine[ 4 ] )
					        .getAdColumnId() );
				}
				if ( referenceTableLine[ 5 ].length() > 0 ) {
					adReferenceTableDTO.setAdKeyId( getColumnByColumnName( adTableDTO, referenceTableLine[ 5 ] )
					        .getAdColumnId() );
				}
				adReferenceTableDTO.setAdReferenceId( adReferenceDTO.getAdReferenceId() );
				adReferenceTableDTO.setAdEntityTypeId( getEntityTypeByName( referenceTableLine[ 7 ].trim() )
				        .getAdEntityTypeId() );

				adReferenceTableDTO = save( adReferenceTableDTO );
			}
		}
	}

	@Override
	protected void handleFillInitialValuesForTable() throws Exception {
		// File Format :
		// Table Name, Name, Description, : 0, 1, 2
		// isDeleteable, isChangeLog, isTranslated, : 3, 4, 5
		// isTranslationTable, Entity Type : 6, 7
		String[][] tableContent = BootstrapUtils.instance().getBootstrapSheetContent( "AdTable" );
		for ( int i = 1; i < tableContent.length; i++ ) {
			final String[] tableLine = tableContent[ i ];
			if ( tableLine.length == 8 ) {
				logger.info( "handleFillInitialValuesForTable() :: getTableByName( " + tableLine[ 0 ] + " )" );
				AdTableDTO adTableDTO = getTableByTableName( tableLine[ 0 ].trim() );
				if ( adTableDTO == null ) {
					adTableDTO = new AdTableDTO();

					adTableDTO.setTableName( tableLine[ 0 ].trim() );
					adTableDTO.setName( tableLine[ 1 ].trim() );
					adTableDTO.setDescription( tableLine[ 2 ].trim() );
					adTableDTO.setIsDeleteable( tableLine[ 3 ].trim().equalsIgnoreCase( "TRUE" )
					        || tableLine[ 3 ].trim().equalsIgnoreCase( "Y" )
					        || tableLine[ 3 ].trim().equalsIgnoreCase( "1" ) );
					adTableDTO.setIsChangeLog( tableLine[ 4 ].trim().equalsIgnoreCase( "TRUE" )
					        || tableLine[ 4 ].trim().equalsIgnoreCase( "Y" )
					        || tableLine[ 4 ].trim().equalsIgnoreCase( "1" ) );
					adTableDTO.setIsTranslated( tableLine[ 5 ].trim().equalsIgnoreCase( "TRUE" )
					        || tableLine[ 5 ].trim().equalsIgnoreCase( "Y" )
					        || tableLine[ 5 ].trim().equalsIgnoreCase( "1" ) );
					adTableDTO.setIsTranslationTable( tableLine[ 6 ].trim().equalsIgnoreCase( "TRUE" )
					        || tableLine[ 6 ].trim().equalsIgnoreCase( "Y" )
					        || tableLine[ 6 ].trim().equalsIgnoreCase( "1" ) );
					adTableDTO.setAdEntityTypeId( getEntityTypeByName( tableLine[ 7 ].trim() ).getAdEntityTypeId() );

					adTableDTO = save( adTableDTO );
				}
			}
		}
	}

	@Override
	protected AdValRuleDTO handleGetValRuleByName(String name) throws Exception {
		return ( AdValRuleDTO ) getAdValRuleDao().findByName( AdValRuleDao.TRANSFORM_ADVALRULEDTO, name );
	}
}